package dbLayer;

import java.util.ArrayList;
import java.sql.*;

import modelLayer.Person;

public class DBPerson implements IFDBPerson {
	
	private Connection con;

	public DBPerson()
	{
		con = DBConnection.getInstance().getDBcon();
	}
	@Override
	public ArrayList<Person> getAllPeople() {
		ResultSet results;
		ArrayList<Person> list = new ArrayList<Person>();
		
		String query = "SELECT * FROM Person";
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
				{
					Person persObj = new Person();
					persObj = buildPerson(results);
					System.out.println(persObj);
					list.add(persObj);
				} // end if
			
			stmt.close();
			
			
		}
		catch(Exception e)
		{
			System.out.println("Exception in the query : " + e);
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<Person> searchPersonbyFName(String fName) {
		
		ResultSet results;
		
		String query = "SELECT * FROM Person WHERE fName = '" + fName + "'";
		System.out.println("Searching for Person with first name : " + fName);
		
		ArrayList<Person> list = new ArrayList<Person>();
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next()){
				Person persObj = new Person();
				persObj = buildPerson(results);
				System.out.println(persObj);
				list.add(persObj);
			} // end if
			stmt.close();
		}
		catch(Exception e)
		{
			System.out.println("Exception in the query : " + e);
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public ArrayList<Person> searchPersonByLName(String lName) {
		return searchMany("lname = '" +lName +"'");
	}

	@Override
	public ArrayList<Person> searchPersonByID(int id) {
		return searchMany("id = '" + id + "'");
	}

	@Override
	public ArrayList<Person> searchPersonByAge(int age) {
		return searchMany("age = '" + age + "'");
	}

	@Override
	public int insertPerson(Person person) throws Exception {
		int nextID = getMax.getMaxId("SELECT max(id) FROM Person");
		nextID = nextID + 1;
		System.out.println("next id is : " + nextID);
		
		int rc = -1;
		
		String query = " INSERT INTO Person(id, fName, lName, age, totalSwag, totalYolo, totalLame, totalCool) VALUES('" +
		nextID + "','" +
		person.getfName() + "','" +
		person.getlName() + "','" +
		person.getAge() + "','" +
		person.getTotalSwag() + "','" +
		person.getTotalYolo() + "','" +
		person.getTotalCool() + "','" +
		person.getTotalLame() + "')";
		
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} //end try
		catch(SQLException ex)
		{
			System.out.println("Person was not inserted, error : " + ex);
			throw new Exception ("Person was not inserted correctly");
		}//end catch
		return rc;
	}

	@Override
	public int updatePerson(Person person)
	{
		Person persObj = person;
		int rc = -1;
		
		String query = "UPDATE Person SET " +
		"fName = '" + person.getfName() + "','" +
		"lName = '" + person.getlName() + "','" +
		"age = '" +	person.getAge() + "','" +
		"totalSwag = '" + person.getTotalSwag() + "','" +
		"totalYolo = '" + person.getTotalYolo() + "','" +
		"totalCool = '" + person.getTotalCool() + "','" +
		"totalLame = '" + person.getTotalLame() + 
		"WHERE id = '" + persObj.getId() + "'";
		
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
		} // end try
		catch ( Exception ex)
		{
			System.out.println(ex);
		} // end catch
		return rc;
		
	}

	@Override
	public int deletePerson(int id) {
		int rc = -1;
		
		String query = "DELETE FROM Person WHERE id = '" + id +"'";
		
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			
		} // end of try
		catch(Exception ex)
		{
			System.out.println("Delete exception : " + ex);
		} // end of catch
		return(rc);
	}
	
	private Person buildPerson(ResultSet results)
	{
		Person persObj = new Person();
		try
		{
			persObj.setfName(results.getString("fName"));
			persObj.setlName(results.getString("lName"));
			persObj.setId(results.getInt("id"));
			persObj.setAge(results.getInt("age"));
			persObj.setTotalCool(results.getFloat("totalCool"));
			persObj.setTotalLame(results.getFloat("totalLame"));
			persObj.setTotalSwag(results.getFloat("totalSwag"));
			persObj.setTotalYolo(results.getFloat("totalYolo"));
			
			
		} // end try
		catch(Exception e)
		{
			System.out.println("could not build the employee, error : " + e);
			
		}
		return persObj;
	}
	
	public ArrayList<Person> searchMany (String wClause)
	{
		ResultSet results;
		ArrayList<Person> list = new ArrayList<Person>();
		
		String query = "SELECT * FROM Person";
		if(wClause.length() > 0)
			query = query + " WHERE " + wClause;
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				Person persObj = new Person();
				persObj = buildPerson(results);
					list.add(persObj);
					
			}
			stmt.close();
		}
		catch(Exception e)
		{
			System.out.println("Exception in query " + e);
			e.printStackTrace();
		}
		return list;
	}
	public ArrayList<Person> searchTop (String wClause)
	{
		ResultSet results;
		ArrayList<Person> list = new ArrayList<Person>();
		
		String query = "SELECT TOP 3 * FROM Person ORDER BY "+wClause;
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			while(results.next())
			{
				Person persObj = new Person();
				persObj = buildPerson(results);
				list.add(persObj);
				
			} // end while
			stmt.close();
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return list;
	}
	@Override
	public ArrayList<Person> searchPersonBySwag() {
		String wClause = "totalSwag DESC";
		return searchTop(wClause);
	}
	@Override
	public ArrayList<Person> searchPersonByYolo() {
		String wClause = "totalYolo DESC";
		return searchTop(wClause);
	}
	@Override
	public ArrayList<Person> searchPersonByCool() {
		String wClause = "totalCool DESC";
		return searchTop(wClause);
	}
	@Override
	public ArrayList<Person> searchPersonByLame() {
		String wClause = "totalLame DESC";
		return searchTop(wClause);
	}


}
